﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Oracle.DataAccess.Client;
using System.Web.UI;
using System.Configuration;

namespace Pension.DAL
{
    public class OracleDBManager
    {
        #region Private variable

        private string connectionString = "";
        private OracleDataAdapter adapter = null;
        private OracleConnection connection = null;
        private OracleCommand command = null;
        private Page objPage = new Page();

        #endregion

        #region Constructor

        public OracleDBManager()
        {
            this.connectionString = ConfigurationManager.ConnectionStrings["PensionConnectionString"].ConnectionString;
        }

        #endregion

        #region Public method

        public OracleConnection Connection
        {
            get { return this.connection; }
        }

        public void OpenConnection()
        {
            this.connection = new OracleConnection(this.connectionString);
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
            }
            connection.Open();
        }

        public DataSet ExecuteQuery(DbParameter oraParams, string storeName)
        {
            DataSet ds = new DataSet();
            try
            {
                this.OpenConnection();

                this.command = new OracleCommand();
                this.command = this.connection.CreateCommand();
                this.command.CommandType = CommandType.StoredProcedure;
                this.command.CommandText = storeName;

                foreach (OracleParameter oraParam in oraParams.Parameters)
                {
                    this.command.Parameters.Add(oraParam);
                }

                this.adapter = new OracleDataAdapter(this.command);
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseConnection();
            }
            return ds;
        }

        public decimal ExecuteNonQuery(DbParameter oraParams, string storeName)
        {
            decimal id = -1;
            int indexOut = 0;
            try
            {
                this.OpenConnection();

                this.command = new OracleCommand();
                this.command = this.connection.CreateCommand();
                this.command.CommandType = CommandType.StoredProcedure;
                this.command.CommandText = storeName;

                foreach (OracleParameter oraParam in oraParams.Parameters)
                {
                    if (oraParam.Direction == ParameterDirection.Output)
                    {
                        indexOut = this.command.Parameters.Count;
                    }
                    this.command.Parameters.Add(oraParam);
                }

                this.command.ExecuteNonQuery();
                if (indexOut != 0)
                {
                    id = Convert.ToDecimal(oraParams.Parameters[indexOut].Value.ToString()); ;
                }
                else
                {
                    id = 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseConnection();
            }
            return id;
        }

        public object ExecuteNonQueryObject(DbParameter oraParams, string storeName)
        {
            object objOut = null;
            int indexOut = 0;
            try
            {
                this.OpenConnection();

                this.command = new OracleCommand();
                this.command = this.connection.CreateCommand();
                this.command.CommandType = CommandType.StoredProcedure;
                this.command.CommandText = storeName;

                foreach (OracleParameter oraParam in oraParams.Parameters)
                {
                    if (oraParam.Direction == ParameterDirection.Output)
                    {
                        indexOut = this.command.Parameters.Count;
                    }
                    this.command.Parameters.Add(oraParam);
                }

                this.command.ExecuteNonQuery();
                if (indexOut != 0)
                {
                    objOut = oraParams.Parameters[indexOut].Value;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseConnection();
            }
            return objOut;
        }

        public void CloseConnection()
        {
            try
            {
                this.connection.Close();

                if (this.adapter != null)
                {
                    this.adapter.Dispose();
                }
                this.command.Dispose();
                this.connection.Dispose();

            }
            catch
            {

            }
        }

        #endregion
    }
}
